﻿using Dapper;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace ZhouYu.DreamTour.Core
{
    public class DapperHelper<T>
    {
        //读取Web配置文件连接字符串
        private static string connString = ConfigurationManager.AppSettings["connString"];


        /// <summary>
        /// 查询第一条
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T QueryFirst(string sql, object param)
        {
            using (MySqlConnection con = new MySqlConnection(connString))
            {
                return con.QueryFirst<T>(sql, param);
            }
        }

       



        /// <summary>
        /// 查询第一条没有返回值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T QueryFirstOrDefault(string sql, object param)
        {
            using (MySqlConnection con = new MySqlConnection(connString))
            {
                return con.QueryFirstOrDefault<T>(sql, param);
            }
        }



        /// <summary>
        /// 查询单条
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T QuerySingle(string sql, object param)
        {
            using (MySqlConnection con = new MySqlConnection(connString))
            {
                return con.QuerySingle<T>(sql, param);
            }
        }



        /// <summary>
        /// 查询单条没有返回值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T QuerySingleOrDefault(string sql, object param)
        {
            using (MySqlConnection con = new MySqlConnection(connString))
            {
                return con.QuerySingleOrDefault<T>(sql, param);
            }
        }



        /// <summary>
        /// 查询集合
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static List<T> QueryList(string sql, object param)
        {
                using (MySqlConnection con = new MySqlConnection(connString))
                {
                    return con.Query<T>(sql, param).ToList();
                }
        }


        /// <summary>
        /// 查询返回Object对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, object param)
        {
            using (MySqlConnection con = new MySqlConnection(connString))
            {
                return con.ExecuteScalar(sql, param);
            }
        }



        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int Execute(string sql, object param)
        {
            using (MySqlConnection con = new MySqlConnection(connString))
            {
                return con.Execute(sql, param);
            }
        }



        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static List<T> QueryPro(string sql, object param)
        {
            using (MySqlConnection con = new MySqlConnection(connString))
            {
                return con.Query<T>(sql, param, null, true, null, CommandType.StoredProcedure).ToList();
            }
        }



        /// <summary>
        /// 执行事务不带参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int ExecuteTransaction(string[] sqlarr)
        {
            int result = 0;
            using (MySqlConnection con = new MySqlConnection(connString))
            {
                using (var tran = con.BeginTransaction())
                {
                    try
                    {
                        foreach (var sql in sqlarr)
                        {
                            result += con.Execute(sql, null, tran);
                        }
                        tran.Commit();
                        return result;
                    }
                    catch (Exception)
                    {
                        tran.Rollback();
                        return 0;
                        throw;
                    }
                }
            }
        }



        /// <summary>
        /// 执行事务带参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int ExecuteTransaction(Dictionary<string, object> keyValues)
        {
            int result = 0;
            using (MySqlConnection con = new MySqlConnection(connString))
            {
                using (var tran = con.BeginTransaction())
                {
                    try
                    {
                        foreach (var sql in keyValues)
                        {
                            result += con.Execute(sql.Key, sql.Value, tran);
                        }
                        tran.Commit();
                        return result;
                    }
                    catch (Exception)
                    {
                        tran.Rollback();
                        return 0;
                        throw;
                    }
                }
            }
        }



        /// <summary>
        /// 单表批量操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int BatchExecute(string sql, List<T> list)
        {
            int result = 0;
            using (MySqlConnection con = new MySqlConnection(connString))
            {
                using (var tran = con.BeginTransaction())
                {
                    try
                    {
                        result = con.Execute(sql, list, tran);
                        tran.Commit();
                        return result;
                    }
                    catch (Exception)
                    {
                        tran.Rollback();
                        return 0;
                        throw;
                    }
                }
            }
        }



        /// <summary>
        /// Ado.Net调用proc
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static string ReaderProc(string sql, SqlParameter[] parameters, string TableName)
        {
            DataSet dataSet = new DataSet();
            MySqlConnection conn = null;
            try
            {
                //创建连接对象
                conn = new MySqlConnection(connString);
                //打开连接
                conn.Open();
                //创建操作对象
                MySqlCommand command = new MySqlCommand(sql, conn);
                //预处理
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }
                //适配器
                MySqlDataAdapter MySqlDataAdapter = new MySqlDataAdapter(command);
                MySqlDataAdapter.Fill(dataSet);
                dataSet.Tables[0].TableName = TableName;
                return builderSql(dataSet.Tables[0]); ;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;
            }
            finally
            {
                conn.Close();
            }
        }



        /// <summary>
        /// 根据DataTable构建插入语句
        /// </summary>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        private static string builderSql(DataTable dataTable)
        {
            StringBuilder builder = new StringBuilder();
            StringBuilder sb = null;
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                sb = new StringBuilder("insert into " + dataTable.TableName + "(");
                StringBuilder head = new StringBuilder();
                StringBuilder values = new StringBuilder();
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {

                    //判断列名是否为Id
                    if (dataTable.Columns[j].ColumnName == "Id")
                    {
                        continue;
                    }

                    //判断是否为最后一位
                    if (j == dataTable.Columns.Count - 1)
                    {
                        head.Append(dataTable.Columns[j].ColumnName);
                    }
                    else
                    {
                        head.Append(dataTable.Columns[j].ColumnName + ",");
                    }
                    //判断是否为最后一位
                    if (j == dataTable.Columns.Count - 1)
                    {
                        //判断是否为空
                        if (dataTable.Rows[i][j] != DBNull.Value)
                        {
                            //判断列类型
                            if (dataTable.Columns[j].DataType.ToString() == "System.String" || dataTable.Columns[j].DataType.ToString() == "System.DateTime")
                            {
                                values.Append("'" + dataTable.Rows[i][j] + "'");
                            }
                            else if (dataTable.Columns[j].DataType.ToString() == "System.Boolean")
                            {
                                if (dataTable.Rows[i][j].ToString() == "True")
                                {
                                    values.Append(1);
                                }
                                else
                                {
                                    values.Append(0);
                                }
                            }
                            else
                            {
                                values.Append(dataTable.Rows[i][j]);
                            }
                        }
                        else
                        {
                            values.Append("null");
                        }
                    }
                    else
                    {
                        //判断是否为空
                        if (dataTable.Rows[i][j] != DBNull.Value)
                        {
                            //判断列类型
                            if (dataTable.Columns[j].DataType.ToString() == "System.String" || dataTable.Columns[j].DataType.ToString() == "System.DateTime")
                            {
                                values.Append("'" + dataTable.Rows[i][j] + "',");
                            }
                            else if (dataTable.Columns[j].DataType.ToString() == "System.Boolean")
                            {
                                if (dataTable.Rows[i][j].ToString() == "True")
                                {
                                    values.Append(1 + ",");
                                }
                                else
                                {
                                    values.Append(0 + ",");
                                }
                            }
                            else
                            {
                                values.Append(dataTable.Rows[i][j] + ",");
                            }
                        }
                        else
                        {
                            values.Append("null,");
                        }
                    }
                }
                builder.Append(sb.Append(head).Append(") VALUES (").Append(values).Append(");\r\n"));
            }
            return builder.ToString();
        }
    }
}
